﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Query2Object;

namespace SqlServer2005
{
    class SqlSession:ISession
    {
        private IDbContext context;

        private SqlConnection conn;

        private SqlCommand comm;

        private SqlDataReader rd;

        private SqlTransaction trans;

        #region ISession 成员

        public IDbContext Context
        {
            get
            {
                return context;
            }
            set
            {
                context = value;
            }
        }
        public void Init(string ConnectionString)
        {
            try
            {
                conn = new SqlConnection(ConnectionString);
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
            }
            catch (SqlException ex)
            {
                throw new SessionException("Init error!", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("Unkown error when init!", ex);
            }
        }

        public IQuery GetQuery(IQueryable QueryParam)
        {
            try
            {
                IQuery query = new SqlQuery();
                query.Session = this;
                query.From(QueryParam);
                return query;
            }
            catch (Exception ex)
            {
                throw new SessionException("Unkown error when Get Query!", ex);
            }
        }

        public bool GetSingle(IQueryable Key)
        {
            try
            {
                string Sql = "SELECT * FROM [" + Key.GetObjectTableName() + "] WHERE [" + Key.GetPrimaryKeyName() + "] = @" + Key.GetPrimaryKeyName();
                PrepareCommand(Sql, Key);
                using (rd = comm.ExecuteReader())
                {
                    if (rd.Read())
                    {
                        Key.LoadFromReader(rd);
                        rd.Close();
                        return true;
                    }
                    else
                    {
                        rd.Close();
                        return false;
                    }

                }
            }
            catch (SqlException ex)
            {
                throw new SessionException("Get Single failt!", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("Unkown error when Get Single!", ex);
            }
        }

        public List<T> ExecuteList<T>(string Sql, IQueryable QueryParam)
        {
            try
            {
                T Data = default(T);
                List<T> rs = new List<T>();
                IQueryable pm = QueryParam;
                PrepareCommand(Sql, QueryParam);
                using (rd = comm.ExecuteReader())
                {
                    while (rd.Read())
                    {
                        Data = (T)QueryParam.CreateNewInstance(); //Activator.CreateInstance<T>();
                        ((IQueryable)Data).LoadFromReader(rd);
                        rs.Add(Data);
                    }
                    rd.Close();
                }
                return rs;
            }
            catch (SqlException ex)
            {
                throw new SessionException("Get List error", ex);
            }
            catch (InvalidOperationException ex)
            {
                throw new SessionException("Invalid Operation when get list", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("Unkown error when get list!", ex);
            }
        }

        public DataTable GetTable(string Sql, IQueryable QueryParam)
        {
            try
            {
                DataTable Data = new DataTable();
                DataRow row = null;
                IQueryable pm = QueryParam;
                PrepareCommand(Sql, QueryParam);
                using (rd = comm.ExecuteReader())
                {
                    for (int i = 0; i < rd.FieldCount; i++)
                    {
                        Data.Columns.Add(rd.GetName(i), rd.GetFieldType(i));
                    }
                    while (rd.Read())
                    {
                        row = Data.NewRow();
                        for (int i = 0; i < rd.FieldCount; i++)
                        {
                            row[i] = rd[i];
                        }
                        Data.Rows.Add(row);
                    }
                    rd.Close();
                }
                return Data;
            }
            catch (SqlException ex)
            {
                throw new SessionException("Get List error", ex);
            }
            catch (InvalidOperationException ex)
            {
                throw new SessionException("Invalid Operation when get list", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("Unkown error when get list!", ex);
            }
        }

        public void ExecuteUpdate(string Sql, IQueryable QueryParam)
        {
            PrepareCommand(Sql, QueryParam);
            comm.ExecuteNonQuery();
        }

        public void Update(IQueryable QueryParam)
        {
            try
            {
                string Pname = "";
                StringBuilder sql = new StringBuilder();
                sql.Append("UPDATE [").Append(QueryParam.GetObjectTableName()).Append("] SET ");
                foreach (IDataParameter p in QueryParam.PreparePrameter())
                {
                    Pname = p.ParameterName.Replace("@", "");
                    if (!Pname.ToLower().Equals(QueryParam.GetPrimaryKeyName().ToLower()))
                    {
                        sql.Append("[").Append(Pname).Append("] = ").Append(p.ParameterName).Append(",");
                    }
                }
                sql = sql.Remove(sql.Length - 1, 1);
                sql.Append(" WHERE [").Append(QueryParam.GetPrimaryKeyName()).Append("] = @").Append(QueryParam.GetPrimaryKeyName());
                PrepareCommand(sql.ToString(), QueryParam);
                comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new SessionException("Update error!", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("unkown error when update!", ex);
            }

        }

        public void Delete(IQueryable QueryParam)
        {
            try
            {
                string Sql = "DELETE FROM [" + QueryParam.GetObjectTableName() + "] WHERE [" + QueryParam.GetPrimaryKeyName() + "] = @" + QueryParam.GetPrimaryKeyName();
                PrepareCommand(Sql, QueryParam);
                comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new SessionException("Delete error!", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("unkown error when Delete!", ex);
            }
        }


        public int ExecuteCount(string Sql, IQueryable QueryParam)
        {
            try
            {
                PrepareCommand(Sql, QueryParam);
                return (int)comm.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                throw new SessionException("Count error!", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("unkown error when count!", ex);
            }
        }

        public void Create(IQueryable QueryParam)
        {
            try
            {
                string sql = QueryParam.GetInsertSql();
                PrepareCommand(sql, QueryParam);
                comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new SessionException("Insert error!", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("unkown error when insert!", ex);
            }
        }

        public void OpenTranscation()
        {
            try
            {
                if (conn.State != ConnectionState.Closed)
                {
                    trans = conn.BeginTransaction();
                }
            }
            catch (Exception ex)
            {
                throw new SessionException("unkown error when open transcation!", ex);
            }
        }

        public void Commit()
        {
            try
            {
                trans.Commit();
                trans = null;
            }
            catch (InvalidOperationException ex)
            {
                throw new SessionException("Invalid Operation in Commit!", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("unkown error when commit!", ex);
            }
        }

        public void RollBack()
        {
            try
            {
                trans.Rollback();
                trans = null;
            }
            catch (InvalidOperationException ex)
            {
                throw new SessionException("Invalid Operation in Rollback!", ex);
            }
            catch (Exception ex)
            {
                throw new SessionException("unkown error when Rollback!", ex);
            }
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            if (trans != null)
            {
                trans.Rollback();
            }
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }

        #endregion

        private void PrepareCommand(string Sql, IQueryable param)
        {
            if (trans != null)
            {
                comm = new SqlCommand(Sql, conn, trans);
            }
            else
            {
                comm = new SqlCommand(Sql, conn);
            }
            foreach (IDataParameter p in param.PreparePrameter())
            {
                comm.Parameters.Add((SqlParameter)p);
            }
        }
    }
}
